package edu.nctu.csie.jichang.database.model.builder;

import java.sql.Types;
import java.util.List;
import java.util.Map;

import edu.nctu.csie.jichang.database.model.cell.ColumnInfo;
import edu.nctu.csie.jichang.database.model.cell.DBTable;
import edu.nctu.csie.jichang.database.model.cell.NameMap;
import edu.nctu.csie.jichang.database.model.cell.NameMapCell;
import edu.nctu.csie.jichang.database.util.StringUtil;

public class PostgreSQLBuilder extends AbstractSQLBuilder {

	protected static final String CHANGE_LINE = StringUtil.CHANGE_LINE;
	protected static final String CHANGE_TAB  = StringUtil.TAB;
	
	protected String symbleEndOfFunction = ";";
	protected String symbleEndOfStatment = ",";
	
	
	public String getSQLSchema(String pSchema,String pSQL) {
		if(StringUtil.isEmptyOrSpace(pSQL)){
			return "/* --- Schema = "+ pSchema +" Without Change --- */";
		}
		StringBuffer tOut = new StringBuffer();
		tOut.append("/* --- Start modify schema = "+ pSchema +" --- */").append(CHANGE_LINE);
		tOut.append("/* USE " + pSchema + " postgresql no this function */").append(CHANGE_LINE);
		tOut.append(pSQL);
		tOut.append("/* --- End  modify schema = "+ pSchema +" --- */").append(CHANGE_LINE);
		return tOut.toString();
	}

	public String getSQLColumnModify(DBTable pTable, ColumnInfo pCurrent,ColumnInfo pBase) {
		
		StringBuffer tOut = new StringBuffer();
		
		// 這邊很特別 先改名 新增 然後刪除改名的那個
		// postgresql 沒有支援修改
		tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
		tOut.append("RENAME COLUMN ").append(pBase.getName()).append(" TO ").append(pBase.getName()+"_temp").append(symbleEndOfFunction).append(CHANGE_LINE);
		tOut.append(getSQLColumnAdd(pTable, pBase));
		tOut.append("UPDATE ").append(pTable.getName()).append(" ");
		tOut.append("SET ").append(pBase.getName()).append(" = CAST(").append(pBase.getName()+"_temp").append(" AS ").append(pBase.getRawTypeName()).append(")").append(symbleEndOfFunction).append(CHANGE_LINE);
		tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
		tOut.append("DROP COLUMN ").append(pBase.getName()+"_temp").append(symbleEndOfFunction).append(CHANGE_LINE);
		
		// 另外設定是否為 NULL
//		tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
//		tOut.append("ALTER COLUMN ").append(pBase.getName()).append(" ").append(getSQLAlterColumnNull(pBase)).append(symbleEndOfFunction).append(CHANGE_LINE);
		
//		if (pBase instanceof ColumnFullInfo) {
//			ColumnFullInfo tInfo = (ColumnFullInfo) pCurrent;
//			if(StringUtil.isNotEmptyOrSpace(tInfo.getNullableName())){
//				tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
//				tOut.append("ADD CONSTRAINT ").append(tInfo.getNullableName()).append(" ");
//				tOut.append("DEFAULT ").append(tInfo.getRawDefault()).append(" ");
//				tOut.append("FOR").append(" ").append(tInfo.getName()).append(symbleEndOfFunction).append(CHANGE_LINE);
//			}
//		}
		
		return tOut.toString();
	}	
	
//	private String getSQLAlterColumnNull(ColumnInfo pColumnInfo) {
//		return (pColumnInfo.isNullable() ? "DROP NOT NULL" : "SET NOT NULL");
//	}

	public String getSQLAlterColumnType(ColumnInfo pColumnInfo) {
		String tOut = pColumnInfo.getRawTypeName();
		if(pColumnInfo.getRawType() == Types.VARCHAR && !"text".equalsIgnoreCase(pColumnInfo.getRawTypeName())){
			tOut += "("+pColumnInfo.getSize()+")";
		}
		return tOut;
	}
	public String getSQLCreateColumnType(ColumnInfo pColumnInfo){
		String tOut = pColumnInfo.getRawTypeName();
		if("serial".equals(pColumnInfo.getRawTypeName())){ // 不用 defalut
			return tOut;
		}
		if(pColumnInfo.getRawType() == Types.VARCHAR && !"text".equalsIgnoreCase(pColumnInfo.getRawTypeName())){
			tOut += "("+pColumnInfo.getSize()+")";
		}
		tOut += " " + (pColumnInfo.isNullable() ? "NULL" : "NOT NULL");
		if(StringUtil.isNotEmptyOrSpace(pColumnInfo.getRawDefault())){
			tOut += " DEFAULT " + pColumnInfo.getRawDefault();
		}
		return tOut;
	}
	public String getSQLIndex(String pTableName,String pName, List<NameMapCell> pColumns) {
		StringBuffer tOut = new StringBuffer();
		tOut.append("CREATE INDEX ").append(pName).append(" ON ").append(pTableName).append("(").append(StringUtil.listToString(pColumns)).append(")");
		return tOut.toString();
	}
	public String getSQLUnique(String pTableName,String pName,List<NameMapCell> pColumns) {
		StringBuffer tOut = new StringBuffer();
		tOut.append("CREATE UNIQUE INDEX ").append(pName).append(" ON ").append(pTableName);
		tOut.append("(").append(StringUtil.listToString(pColumns)).append(")");
		return tOut.toString();
	}
	
	public String dropIndex(DBTable pTable, NameMap pCurrent,NameMap pBase) {
		StringBuffer tOut = new StringBuffer();
		if(!pCurrent.isSame(pBase)){
			Map<String,List<NameMapCell>> tKey = pCurrent.getSubstract(pBase);
			for (String s : tKey.keySet()) {
				tOut.append("DROP INDEX ").append(s).append(symbleEndOfFunction).append(CHANGE_LINE);
			}
		}
		return tOut.toString();
	}

	public String dropUnique(DBTable pTable, NameMap pCurrent, NameMap pBase) {
		return dropIndex(pTable, pCurrent, pBase);
	}
}